package impl;

import bean.UserInfo;
import bean.UserInfor;
import conts.PageConts;
import dao.UserDAO;
import util.DBHelper;

import java.sql.*;
import java.sql.Date;
import java.util.*;

/**
 * @classname:UserDaoImpl
 * @description:
 * @author: Mr.Z
 * @date: 2020/12/28 17:20
 * @version: 0.1
 * @since:1.8
 */
public class UserDaoImpl implements UserDAO {
    private Connection conn;
    private PreparedStatement ps;
    private String sql;
    private Integer result;
    private ResultSet rs;

    //添加单个学生
    @Override
    public int addUser() {
        DBHelper.getCon();
        sql = "insert into tb_userinfor (name,age,balance,image,birthday,roleid,status) value (\n" +
                "  '张磊',27,3800,'zhao.jpg','2020-12-12',6,0);";
        try {
            ps = conn.prepareStatement(sql);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;

    }

    //添加单个学生信息从前端传入
    @Override
    public int addUserParam(UserInfor userInfor) {
        conn = DBHelper.getCon();
        String sql = "insert into tb_userinfor (name,age,balance,image,birthday,roleid,status) value (?,?,?,?,?,?,?);";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, userInfor.getName());
            ps.setInt(2, userInfor.getAge());
            ps.setBigDecimal(3, userInfor.getBalance());
            ps.setString(4, userInfor.getImage());
            ps.setDate(5, new Date(userInfor.getBirthday().getTime()));
            ps.setInt(6, userInfor.getRoleid());
            ps.setInt(7, userInfor.getStatus());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;

    }


    //信息从前段传入表不同
    @Override
    public int addUserParams(UserInfo userInfo) {
        conn = DBHelper.getCon();
        String sql = "insert into tb_userinfo(name,age,pass,balance,image,birthday,roleid,status) value (?,?,?,?,?,?,?,?)";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, userInfo.getName());
            ps.setInt(2, userInfo.getAge());
            ps.setString(3, userInfo.getPass());
            ps.setBigDecimal(4, userInfo.getBalance());
            ps.setString(5, userInfo.getImage());
            ps.setDate(6, new Date(userInfo.getBirthday().getTime()));
            ps.setInt(7, userInfo.getRoleid());
            ps.setInt(8, userInfo.getStatus());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;
    }


    //通过个Id删除用户
    @Override
    public int deleteUserById(int id) {

        conn = DBHelper.getCon();

        sql = "delete  from tb_userinfor where id=?;";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;
    }

    //删除多个用户 in字符串拼接
    @Override
    public int deleteUserMany(int[] ids) {
        conn = DBHelper.getCon();
        StringBuilder builder = new StringBuilder();
        int length = ids.length;
        int count = 0;
        for (int id : ids) {
            count++;
            builder.append(id);
            if (count != length) {
                builder.append(",");
            }
        }
        sql = "delete  from tb_userinfor where id in(" + builder + ")";
        try {
            ps = conn.prepareStatement(sql);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;
    }


    //通过id查询用户信息
    @Override
    public UserInfor selectUserById(int id) {
        conn = DBHelper.getCon();
        sql = "select * from tb_userinfor where id=?";
        UserInfor userInfor = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                userInfor = new UserInfor(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps, rs);
        }
        return userInfor;
    }

    //查询所有的用户信息
    @Override
    public List<UserInfor> selectAll() {
        conn = DBHelper.getCon();
        sql = "select * from tb_userinfor";
        List list = new ArrayList(10);
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                UserInfor userInfor = new UserInfor(rs);
                list.add(userInfor);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }


    //更新用户信息通过id
    @Override
    public int UpdateUserById(UserInfor userInfor) {
        conn = DBHelper.getCon();
        sql = "update tb_userinfor set name=?,age=?,balance=?,image=?,birthday=?,roleid=?,status=? where id=?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, userInfor.getName());
            ps.setInt(2, userInfor.getAge());
            ps.setBigDecimal(3, userInfor.getBalance());
            ps.setString(4, userInfor.getImage());
            ps.setDate(5, new Date(userInfor.getBirthday().getTime()));
            ps.setInt(6, userInfor.getRoleid());
            ps.setInt(7, userInfor.getStatus());
            ps.setInt(8, userInfor.getId());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;

    }


    //分页查询 每页的容量通过常量控制
    @Override
    public List<UserInfor> selectUserByPage(int page) {
        conn = DBHelper.getCon();
        int pageSize = PageConts.PAGE_SIZE;
        sql = " select * from tb_userinfor limit ?,?";
        List<UserInfor> list = new ArrayList<>(10);
        try {
            //这个很重要！！
            int pageNum = (page - 1) * pageSize;
            ps = conn.prepareStatement(sql);
            ps.setInt(1, pageNum);
            ps.setInt(2, pageSize);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new UserInfor(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps, rs);
        }
        return list;
    }

    //查询重页数 返回的是long类型的数据
    @Override
    public long selectCountPage() {
        conn = DBHelper.getCon();
        long num = 0;
        sql = "select count(*) from tb_userinfor";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                num = rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    int count = 0;


    //多条件查询通过Stringbuilder凭凭借sql语句
    @Override
    public List<UserInfor> selectUserByParams(Map<String, Object> map) {
        conn = DBHelper.getCon();
        StringBuilder builder = new StringBuilder("select * from tb_userinfor where ");
        map.forEach((k, v) -> {
            count++;
            builder.append(k);
            builder.append("=");
            builder.append("'");
            builder.append(v);
            builder.append("'");
            //两个空格
            if (count < map.size()) {
                builder.append(" and ");
            }
        });
        List<UserInfor> list = new ArrayList<>(10);
        try {
            ps = conn.prepareStatement(builder.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new UserInfor(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps, rs);
        }
        return list;
    }


    //多表联查同个Id  查询出两条信息所以用List<Map<String, Object>> Map<String,Map<String, Object>> 但是这个map的key必须是唯一的
    @Override
    public List<Map<String, Object>> selectUserRoleAndPer(int uid) {
        conn = DBHelper.getCon();

        List<Map<String, Object>> list = new ArrayList<>(10);
        //在多表查询的情况下出现重复的id尽可能把重复的字段起别名字
        sql = "select\n" +
                "    u.id as uid,u.name,u.age,u.image,r.rolename,rp.pid as pid,p.path\n" +
                "  from tb_userinfor as u,tb_role as r,tb_role_per as rp,tb_permission as p\n" +
                "  where u.id=? and u.roleid=r.id and r.id=rp.roleid and rp.pid=p.id;\n" +
                "\n";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, uid);
            rs = ps.executeQuery();
            while (rs.next()) {
                Map<String, Object> map = new LinkedHashMap<>(16);
                map.put("uid", rs.getObject("uid"));
                map.put("name", rs.getObject("name"));
                map.put("age", rs.getObject("age"));
                map.put("image", rs.getObject("image"));
                map.put("rolename", rs.getObject("rolename"));
                map.put("pid", rs.getObject("pid"));
                map.put("path", rs.getObject("path"));
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps, rs);
        }
        return list;
    }
}
